// .........................................................................
// Title: interest_details.do
//
// Consolidates bond coupon interest details information from multiple sources
// .........................................................................

* --------------------------------------
* Consolidate coupon interest details
* --------------------------------------

* coupon details from mergent
use "$raw/mergent/mergent_combined_issue.dta", clear
foreach var of varlist * {
    local x = lower("`var'")
    rename `var' `x'
}
keep complete_cusip coupon coupon_type first_interest_date interest_frequency offering_date maturity
rename complete_cusip cusip
collapse (firstnm) coupon coupon_type first_interest_date interest_frequency offering_date maturity, by(cusip)
save "$tmp/mergent_coupon_details", replace

* coupon details from trace master
use "$raw/trace/trace_corp_master", clear
rename cusip_id cusip
drop if missing(cusip)
collapse (firstnm) cpn_rt cpn_type_cd mtrty_dt, by(cusip)
rename cpn_rt coupon
rename cpn_type_cd coupon_type
rename mtrty_dt maturity_date
save "$tmp/trace_corp_master_coupon_details", replace

* coupon details from dealogic
use "$tmp/dealogic_dcm_issuance_complete", clear
keep cusip couponfrequencyid couponpercent firstcoupondate _maturitydate isfloatingrate
replace firstcoupondate = substr(firstcoupondate, 1, 10)
gen first_interest_date = date(firstcoupondate, "YMD")
format %td first_interest_date
drop firstcoupondate
rename couponpercent coupon
rename _maturitydate maturity_date
qui mmerge couponfrequencyid using "$raw/dealogic/Frequency.dta", unmatched(m) umatch(id)
drop _merge
gen interest_frequency = .
replace interest_frequency = 1 if name == "Annual"
replace interest_frequency = 12 if name == "Monthly"
replace interest_frequency = 4 if name == "Quarterly"
replace interest_frequency = 2 if name == "Semi-Annual"
drop couponfrequencyid code name
drop if missing(cusip)
collapse (firstnm) coupon maturity_date first_interest_date interest_frequency isfloatingrate, by(cusip)
save "$tmp/dealogic_coupon_details", replace

* ciq
use "$tmp/ciq_static_characteristics_processed", clear
keep cusip iq_offer_coupon bc_is_floating iq_pmt_freq iq_maturity_date iq_offer_date iq_first_int_date
gen interest_frequency = .
replace interest_frequency = 1 if iq_pmt_freq == "Annual"
replace interest_frequency = 12 if iq_pmt_freq == "Monthly"
replace interest_frequency = 4 if iq_pmt_freq == "Quarterly"
replace interest_frequency = 2 if iq_pmt_freq == "Semi-Annual"
drop iq_pmt_freq
cap destring c_iq_offer_coupon, replace
save "$tmp/ciq_coupon_details", replace

* secmaster
use cusip maturity_date issuance_date coupon_percent class_code1 using "$raw/cmns/gcap_security_master_cusip.dta" if class_code1 == "B", clear
drop if missing(maturity_date) & missing(issuance_date) & missing(coupon_percent)
save "$tmp/secm_coupon_details", replace

* consolidate
use "$tmp/dealogic_coupon_details", clear
gen _isfloatingrate = 1 if isfloatingrate == "true"
replace _isfloatingrate = 0 if isfloatingrate == "false"
drop isfloatingrate
rename _isfloatingrate isfloatingrate
mmerge cusip using "$tmp/mergent_coupon_details", uname(m_)
mmerge cusip using "$tmp/trace_corp_master_coupon_details", uname(t_)
mmerge cusip using "$tmp/ciq_coupon_details", uname(c_)
mmerge cusip using "$tmp/secm_coupon_details", uname(s_)
drop _merge
drop s_class_code1

cap destring c_iq_offer_coupon, replace
replace coupon = m_coupon if missing(coupon)
replace coupon = s_coupon_percent if missing(coupon)
replace coupon = c_iq_offer_coupon if missing(coupon)
replace coupon = t_coupon if missing(coupon)
drop m_coupon s_coupon_percent c_iq_offer_coupon t_coupon

replace maturity_date = m_maturity if missing(maturity_date)
replace maturity_date = s_maturity_date if missing(maturity_date)
replace maturity_date = c_iq_maturity_date if missing(maturity_date)
replace maturity_date = t_maturity_date if missing(maturity_date)
drop m_maturity s_maturity_date c_iq_maturity_date t_maturity_date

replace first_interest_date = m_first_interest_date if missing(first_interest_date)
replace first_interest_date = c_iq_first_int_date if missing(first_interest_date)
drop m_first_interest_date c_iq_first_int_date

cap destring m_interest_frequency, replace
replace interest_frequency = m_interest_frequency if missing(interest_frequency)
replace interest_frequency = c_interest_frequency if missing(interest_frequency)
drop m_interest_frequency c_interest_frequency

rename c_bc_is_floating bc_is_floating
replace bc_is_floating = isfloatingrate if missing(bc_is_floating)

gen m_is_floating = .
replace m_is_floating = 0 if m_coupon_type == "F"
replace m_is_floating = 1 if m_coupon_type == "V"
replace bc_is_floating = m_is_floating if missing(bc_is_floating)
drop isfloatingrate m_is_floating m_coupon_type

keep cusip coupon maturity_date first_interest_date interest_frequency bc_is_floating
drop if missing(coupon) & missing(first_interest_date) & missing(interest_frequency) & missing(maturity_date) & missing(bc_is_floating)
drop if missing(cusip)
save "$tmp/interest_details_consolidated", replace

* --------------------------------------
* Also save other consolidated dummies
* --------------------------------------

* dealogic dummies
use "$tmp/dealogic_dcm_issuance_complete.dta", clear
keep cusip issubordinated iscallable
drop if missing(cusip)
fcollapse (firstnm) issubordinated iscallable, by(cusip)
gen senior = 0 if issubordinated == "true"
replace senior = 1 if issubordinated == "false"
gen callable = 1 if iscallable == "true"
replace callable = 0 if iscallable == "false"
drop issubordinated iscallable
drop if missing(senior) & missing(callable)
tempfile dlg_dummies
save `dlg_dummies', replace

* consolidate
use "$tmp/ciq_static_characteristics_processed", clear
keep cusip bc_is_senior bc_is_callable
mmerge cusip using `dlg_dummies'
replace bc_is_senior = senior if missing(bc_is_senior)
replace bc_is_callable = callable if missing(bc_is_callable)
mmerge cusip using "$tmp/interest_details_consolidated.dta", ukeep(bc_is_floating)
drop if missing(cusip)
drop if missing(bc_is_senior) & missing(bc_is_callable) & missing(bc_is_floating)
drop _merge senior callable
save "$tmp/consolidated_static_dummies", replace
